﻿SET QUOTED_IDENTIFIER ON
GO
SET ARITHABORT ON
GO
SET NUMERIC_ROUNDABORT OFF
GO
SET CONCAT_NULL_YIELDS_NULL ON
GO
SET ANSI_NULLS ON
GO
SET ANSI_PADDING ON
GO
SET ANSI_WARNINGS ON
GO


CREATE TABLE [dbo].[bht_ColumnsVirtual](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[ColumnName] [nvarchar](400) NOT NULL,
	[Description] [nvarchar](400) NULL,
	[HideForEdition] [bit] NOT NULL,
	[ViewControl] [nvarchar](200) NOT NULL,
	[EditControl] [nvarchar](200) NOT NULL,
 CONSTRAINT [PK_bht_ColumnsVirtual] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[bht_ColumnsVirtual] ADD  CONSTRAINT [DF_bht_ColumnsVirtual_HideForEdition]  DEFAULT ((0)) FOR [HideForEdition]
GO


CREATE TABLE [dbo].[bht_ColumnsVirtualSet](
	[VirtualId] [int] NOT NULL,
	[ColumnId] [int] NOT NULL
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[bht_ColumnsVirtualSet]  WITH CHECK ADD  CONSTRAINT [FK_bht_ColumnsVirtualSet_bht_ColumnsVirtual] FOREIGN KEY([VirtualId])
REFERENCES [dbo].[bht_ColumnsVirtual] ([Id])
ON UPDATE CASCADE
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[bht_ColumnsVirtualSet] CHECK CONSTRAINT [FK_bht_ColumnsVirtualSet_bht_ColumnsVirtual]
GO


CREATE PROCEDURE [dbo].[bhp_NemoGroupColumnsGetVirtual] (
	@GroupId	INT
)
AS
BEGIN

	SELECT DISTINCT
		vc.*,
		STUFF((SELECT ',' + CAST(ColumnId as NVARCHAR(10)) FROM bht_ColumnsVirtualSet vcs WHERE vcs.VirtualId = vcs.VirtualId FOR XML PATH('')), 1, 1, '') as JoinedColumns
	FROM
		bht_ColumnsVirtual vc
		INNER JOIN bht_ColumnsVirtualSet vcs ON vc.Id = vcs.VirtualId
		INNER JOIN n_Tree t ON vcs.ColumnId = t.id
	WHERE
		t.parent = @GroupId

END



GO
